﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spStg_IndexUsage]
#-- Purpose:		Updates index usage activity in the Warehouse
#--	Last Update:	12/01/2015
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spStg_IndexUsage]
AS

SET NOCOUNT ON;

--- Lock the staging tables
SELECT TOP 1 * FROM [dbo].[tblStg_IndexUsage] WITH (TABLOCK)
SELECT TOP 1 * FROM [dbo].[tblStg_DbTblInd_Names] WITH (TABLOCK)

-------------------------------------------------------------------
--- UPDATE EXISTING:  Index Stats
---
UPDATE		whl
SET			ius_type = mon.ius_type,
			ius_is_disabled = mon.ius_is_disabled,
			ius_user_updates = mon.ius_user_updates,
			ius_user_uses = mon.ius_user_uses,
			ius_system_updates = mon.ius_system_updates,
			ius_system_uses = mon.ius_system_uses,
			ius_is_pkey = mon.ius_is_pkey,
			ius_data_space_id = mon.ius_data_space_id
FROM		[dbo].[tblStg_IndexUsage] mon
JOIN		[dbo].[tblWH_IndexUsage] whl ON mon.ius_sql_starttime = whl.ius_sql_starttime
			AND mon.ius_databaseid = whl.ius_databaseid
			AND mon.ius_tableid = whl.ius_tableid
			AND mon.ius_indexid = whl.ius_indexid
			AND mon.ius_server_ssd_id = whl.ius_server_ssd_id
WHERE		NOT (
			mon.ius_type = whl.ius_type
			AND mon.ius_is_disabled = whl.ius_is_disabled
			AND mon.ius_user_updates = whl.ius_user_updates
			AND mon.ius_user_uses = whl.ius_user_uses
			AND mon.ius_system_updates = whl.ius_system_updates
			AND mon.ius_system_uses = whl.ius_system_uses
			AND mon.ius_is_pkey = whl.ius_is_pkey
			AND mon.ius_data_space_id = whl.ius_data_space_id
			)

INSERT INTO [dbo].[tblWH_IndexUsage]
	(
	ius_server_ssd_id,
	ius_sql_starttime,
	ius_databaseid,
	ius_tableid,
	ius_indexid,
	ius_type,
	ius_is_disabled,
	ius_user_updates,
	ius_user_uses,
	ius_system_updates,
	ius_system_uses,
	ius_is_pkey,
	ius_data_space_id
	)
SELECT		mon.ius_server_ssd_id,
			mon.ius_sql_starttime,
			mon.ius_databaseid,
			mon.ius_tableid,
			mon.ius_indexid,
			mon.ius_type,
			mon.ius_is_disabled,
			mon.ius_user_updates,
			mon.ius_user_uses,
			mon.ius_system_updates,
			mon.ius_system_uses,
			mon.ius_is_pkey,
			mon.ius_data_space_id
FROM		[dbo].[tblStg_IndexUsage] mon
LEFT JOIN	[dbo].[tblWH_IndexUsage] whl ON mon.ius_sql_starttime = whl.ius_sql_starttime
			AND mon.ius_databaseid = whl.ius_databaseid
			AND mon.ius_tableid = whl.ius_tableid
			AND mon.ius_indexid = whl.ius_indexid
			AND mon.ius_server_ssd_id = whl.ius_server_ssd_id
WHERE		whl.ius_sql_starttime IS NULL
ORDER BY	mon.ius_sql_starttime,
			mon.ius_databaseid,
			mon.ius_tableid,
			mon.ius_indexid

-------------------------------------------------------------------
--- UPDATE EXISTING:  Name - Database, Table, Index
---
UPDATE		whl
SET			[btw_enddate] = mon.[bti_enddate]
FROM		[dbo].[tblStg_DbTblInd_Names] mon
JOIN		[dbo].[tblWH_DbTblInd_Names] whl ON mon.[bti_databaseid] = whl.[btw_databaseid]
			AND mon.[bti_tableid] = whl.[btw_tableid]
			AND ISNULL(mon.[bti_indexid], 0) = ISNULL(whl.[btw_indexid], 0)
			AND mon.[bti_databasename] = whl.[btw_databasename]
			AND mon.[bti_tablename] = whl.[btw_tablename]
			AND ISNULL(mon.[bti_indexname], SPACE(0)) = ISNULL(whl.[btw_indexname], SPACE(0))
			AND mon.[bti_server_ssd_id] = whl.[btw_server_ssd_id]
			AND mon.[bti_startdate] = whl.[btw_startdate]
WHERE		NOT (
				whl.[btw_enddate] = mon.[bti_enddate]
				OR (
					whl.[btw_enddate] IS NULL
					AND mon.[bti_enddate] IS NULL
				)
			)

INSERT INTO [dbo].[tblWH_DbTblInd_Names]
	(
	[btw_server_ssd_id],
	[btw_startdate],
	[btw_databaseid],
	[btw_tableid],
	[btw_indexid],
	[btw_databasename],
	[btw_tablename],
	[btw_indexname]
	)
SELECT		mon.[bti_server_ssd_id],
			mon.[bti_startdate],
			mon.[bti_databaseid],
			mon.[bti_tableid],
			mon.[bti_indexid],
			mon.[bti_databasename],
			mon.[bti_tablename],
			mon.[bti_indexname]
FROM		[dbo].[tblStg_DbTblInd_Names] mon
LEFT JOIN	[dbo].[tblWH_DbTblInd_Names] whl ON mon.[bti_databaseid] = whl.[btw_databaseid]
			AND mon.[bti_tableid] = whl.[btw_tableid]
			AND ISNULL(mon.[bti_indexid], 0) = ISNULL(whl.[btw_indexid], 0)
			AND mon.[bti_databasename] = whl.[btw_databasename]
			AND mon.[bti_tablename] = whl.[btw_tablename]
			AND ISNULL(mon.[bti_indexname], SPACE(0)) = ISNULL(whl.[btw_indexname], SPACE(0))
			AND mon.[bti_server_ssd_id] = whl.[btw_server_ssd_id]
WHERE		whl.[btw_server_ssd_id] IS NULL
ORDER BY	mon.[bti_startdate],
			mon.[bti_databaseid],
			mon.[bti_tableid],
			mon.[bti_indexid],
			mon.[bti_databasename],
			mon.[bti_tablename],
			mon.[bti_indexname]
			

-------------------------------------------------------------------
--- If there is no more data in the table, truncate to reset
---		the identity value
---
TRUNCATE TABLE [dbo].[tblStg_IndexUsage]
TRUNCATE TABLE [dbo].[tblStg_DbTblInd_Names]

SET NOCOUNT OFF;